package org.qdao.implement.oracle;

import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URL;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.qdao.IDBEngine;
import org.qdao.IEntityDao;
import org.qdao.IFetchHandler;
import org.qdao.TableDescriptor;
import org.qdao.annotation.Column;
import org.qdao.annotation.PrimaryKey;
import org.qdao.util.SQLTigger;

/**
 * 
 * @author 譚元吉
 * @since 2010/02/06 11:00:50
 */
// @SuppressWarnings("")
public class OracleEntityDao<T> implements InvocationHandler, IEntityDao<T> {

	private IDBEngine dbEngine;

	private PreparedStatement pstmt;

	private ResultSet rs;

	private Class<?> entity;

	private Logger logger = Logger.getLogger("OracleEntityDaoLogger");

	private StringBuffer sql = new StringBuffer();

	private boolean isLogger = true;

	public OracleEntityDao(IDBEngine dbEngine, Class<?> entity) {
		this.dbEngine = dbEngine;
		this.entity = entity;
	}

	public OracleEntityDao(IDBEngine dbEngine, Class<?> entity, boolean isLogger) {
		this.dbEngine = dbEngine;
		this.entity = entity;
		this.isLogger = isLogger;
	}

	public IDBEngine getDBEngine() {
		return this.dbEngine;
	}

	
	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
		// beforeInvoke(method);

		Object result = method.invoke(this, args);

		log(sql.toString());

		// afterInvoke(result);

		close();
		return result;
	}

	private void log(String info) {
		// log the sql
		if (isLogger && info != null && info.trim().length() != 0) {
			logger.log(Level.INFO, "{\r\n\t" + info + ";\r\n}");
		}
	}

	
	public Integer count() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		int result = -1;
		sql.append("SELECT COUNT(*) FROM ").append(table.name);
		try {
			prepare();
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}

	
	public Integer count(String sqlPart) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		int result = -1;
		sql.append("SELECT COUNT(*) FROM ").append(table.name).append(" WHERE ").append(sqlPart);
		try {
			pstmt = getDBEngine().getConnection().prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return result;
	}

	
	public boolean create() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append(SQLTigger.getOracleCreateTigger(table));
		try {
			pstmt = getDBEngine().getConnection().prepareStatement(sql.toString());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean delete(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据部匹配!");
			return false;
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("DELETE FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
					return false;
				} catch (IllegalAccessException e) {
					e.printStackTrace();
					return false;
				}
			}
		}
		try {
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean delete(List<T> datas) {
		if (datas == null || datas.size() == 0) {
			logWarning("数据未空或集合中没有数据");
			// logWarning("Datas is null or datas's length is zero!");
			return false;
		}
		Iterator<?> iter = datas.iterator();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		Field[] fs = entity.getDeclaredFields();
		if (fs.length != table.columns.length) {
			// logger.log(Level.WARNING,
			// "The object's fields can not match the table's columns!");
			logWarning(" 插入的数据项与表中的项不匹配");
			return false;
		}
		sql.append("DELETE FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		while (iter.hasNext()) {
			Object o = iter.next();
			Class<?> clazz = o.getClass();
			Field[] fields = clazz.getDeclaredFields();
			int count = 0;
			for (int i = 0; i < fields.length; i++) {
				if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
					try {
						Object value = fs[i].get(o);
						setValue(pstmt, count + 1, fs[i], value, table);
						count++;
					} catch (SQLException e) {
						e.printStackTrace();
						return false;
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
						return false;
					} catch (IllegalAccessException e) {
						e.printStackTrace();
						return false;
					}
				}
			}
			try {
				pstmt.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		try {
			int[] results = pstmt.executeBatch();
			pstmt.clearBatch();
			for (int i : results) {
				if (i < 0) {
					return false;
				}
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean deletePart(String sqlPart) {
		// TODO Auto-generated method stub
		return false;
	}

	/**
	 * 对List数据做检查
	 * 
	 * @author tan
	 * @param datas
	 * @return 2010/03/03 10:05:26
	 */
	private boolean checkList(List<T> datas) {
		if (datas == null || datas.size() == 0) {
			logWarning("数据未空或集合中没有数据");
			// logWarning("Datas is null or datas's length is zero!");
			return false;
		}
		return true;
	}

	
	public boolean drop() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("DROP TABLE ").append(table.name);
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean drop(boolean cascade) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		if (cascade) {
			sql.append("DROP TABLE ").append(table.name).append(" CASCADE CONSTRAINTS");
		} else {
			sql.append("DROP TABLE ").append(table.name);
		}
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean insert(T dto) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		createInsertTigger(table);
		try {
			prepare();
			prepareStatement(table, dto);
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return false;
	}

	
	public boolean insert(List<?> dtos) {
		if (dtos == null || dtos.size() == 0) {
			logWarning("数据未空或集合中没有数据");
			// logWarning("Datas is null or datas's length is zero!");
			return false;
		}
		Iterator<?> iter = dtos.iterator();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		Field[] fs = entity.getDeclaredFields();
		if (fs.length != table.columns.length) {
			// logger.log(Level.WARNING,
			// "The object's fields can not match the table's columns!");
			logWarning(" 插入的数据项与表中的项不匹配");
			return false;
		}
		createInsertTigger(table);
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		} catch (InstantiationException e1) {
			e1.printStackTrace();
			return false;
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
			return false;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
			return false;
		}
		while (iter.hasNext()) {
			Object o = iter.next();
			try {
				prepareStatement(table, o);
				pstmt.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
				return false;
			} catch (IllegalAccessException e) {
				e.printStackTrace();
				return false;
			}
		}
		try {
			int[] results = pstmt.executeBatch();
			pstmt.clearBatch();
			for (int i : results) {
				if (i < 0) {
					return false;
				}
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	private void logWarning(String message) {
		logger.log(Level.WARNING, message);
	}

	
	public boolean exist() {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT ROWID FROM ").append(table.name);
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			if (errorCode == 942) {
				logInfo("Table or View is not exist!");
			}
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean lock(boolean share) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		if (share)
			sql.append("LOCK TABLE ").append(table.name).append(" IN SHARE MODE");
		else
			sql.append("LOCK TABLE ").append(table.name).append(" IN EXCLUSIVE MODE");
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public boolean lock(int timeout) {
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		if (timeout <= 0)
			sql.append("SELECT * FROM ").append(table.name).append(" FOR UPDATE");
		else
			sql.append("SELECT * FROM ").append(table.name).append(" FOR UPDATE " + timeout);
		try {
			prepare();
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			return false;
		} catch (InstantiationException e) {
			e.printStackTrace();
			return false;
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
	}

	
	public T lock(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		sql.append(" FOR UPDATE NOWAIT");
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Field[] fields = key.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(key, rs.getObject(i + 1));
					}
				}
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return key;
	}

	
	public T lock(T key, int timeout) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// append the timeout
		if (timeout <= 0)
			sql.append(" FOR UPDATE");
		else
			sql.append(" FOR UPDATE " + timeout);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Field[] fields = key.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(key, rs.getObject(i + 1));
					}
				}
				break;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return key;
	}

	
	public List<T> lock(String sqlPart) {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ")
				.append(sqlPart).append(" FOR UPDATE");
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(result, rs.getObject(i + 1));
					}
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public List<T> lock(String sqlPart, int timeout) {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ")
				.append(sqlPart).append(" FOR UPDATE ");

		// add the timeout
		if (timeout >= 0)
			sql.append(timeout);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(result, rs.getObject(i + 1));
					}
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public boolean match(Class<? extends T> table) {
		if (table == entity) {
			return true;
		}
		return false;
	}

	
	public T select(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name).append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			if (rs.next()) {
				Field[] fields = key.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					if (fields[i].getType() == rs.getObject(i + 1).getClass()) {
						fields[i].set(key, rs.getObject(i + 1));
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return key;
	}

	
	public List<T> select() {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					fields[i].set(result, rs.getObject(i + 1));
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public List<T> select(IFetchHandler<T> filter) {
		// TODO filter begin
		filter.begin();
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		try {
			rs = pstmt.executeQuery();

			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					fields[i].set(result, rs.getObject(i + 1));
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		// TODO filter end
		filter.end();
		return list;

	}

	
	public List<T> select(String sqlPart) {
		List<T> list = new ArrayList<T>();
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		sql.append("SELECT * FROM ").append(table.name)
				.append(" WHERE ").append(sqlPart);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					fields[i].set(result, rs.getObject(i + 1));
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}

	
	public List<T> select(String sqlPart, IFetchHandler<T> filter) {
		return null;
	}

	
	public T update(T key) {
		Class<?> clazz = key.getClass();
		if (entity != clazz) {
			// logWarning("Your data is wrong!");
			logWarning("数据不匹配!");
		}
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		// generate the update sql
		createUpdateTigger(table);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		Field[] fs = clazz.getDeclaredFields();
		int count = 0;
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(Column.class)) {
				try {
					Object value = fs[i].get(key);
					if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
						// primaryKeys[0] = value;
					}
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
		for (int i = 0; i < fs.length; i++) {
			if (fs[i].isAnnotationPresent(PrimaryKey.class)) {
				try {
					Object value = fs[i].get(key);
					setValue(pstmt, count + 1, fs[i], value, table);
					count++;
				} catch (SQLException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}

		try {
			pstmt.executeUpdate();
			sql = new StringBuffer();
			log(sqlInfo.toString());
			return select(key);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		return null;
	}

	private void createUpdateTigger(TableDescriptor table) {
		sql.append("UPDATE ").append(table.name).append(" SET ");
		for (int i = 0; i < table.columns.length; i++) {
			if (i != table.columns.length - 1) {
				sql.append(table.columns[i].name).append(" = ?,");
			} else
				sql.append(table.columns[i].name).append(" = ?");
		}
		sql.append(" WHERE ");
		for (int i = 0; i < table.primaryKeys.length; i++) {
			if (i != table.primaryKeys.length - 1) {
				sql.append(table.primaryKeys[i]).append(" = ? AND ");
			} else
				sql.append(table.primaryKeys[i]).append(" = ?");
		}
		// TODO test log the sql information
		sqlInfo = new StringBuilder(sql);
	}

	
	public boolean update(List<T> datas) {
		if (datas == null || datas.size() == 0) {
			logWarning("数据未空或集合中没有数据");
			// logWarning("Datas is null or datas's length is zero!");
			return false;
		}
		Iterator<?> iter = datas.iterator();
		Connection connection = null;
		TableDescriptor table = dbEngine.getTableDescriptor(entity);
		// generate the update sql
		createUpdateTigger(table);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		while (iter.hasNext()) {
			Object o = iter.next();
			Class<?> clazz = o.getClass();
			Field[] fields = clazz.getDeclaredFields();
			int count = 0;
			for (int i = 0; i < fields.length; i++) {
				if (fields[i].isAnnotationPresent(Column.class)) {
					try {
						Object value = fields[i].get(o);
						setValue(pstmt, count + 1, fields[i], value, table);
						count++;
					} catch (SQLException e) {
						e.printStackTrace();
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					}
				}
			}
			for (int i = 0; i < fields.length; i++) {
				if (fields[i].isAnnotationPresent(PrimaryKey.class)) {
					try {
						Object value = fields[i].get(o);
						setValue(pstmt, count + 1, fields[i], value, table);
						count++;
					} catch (SQLException e) {
						e.printStackTrace();
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					}
				}
			}
			try {
				pstmt.addBatch();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		try {
			connection = pstmt.getConnection();
			int[] results = pstmt.executeBatch();
			connection.commit();
			pstmt.getConnection().commit();
			pstmt.clearBatch();
			for (int i : results) {
				if (i < 0) {
					return false;
				}
			}
			return true;
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return false;
		}
	}

	public void close() {
		if (sql.length() >= 0) {
			sql = new StringBuffer();
		}
		if (pstmt != null) {
			try {
				pstmt.close();
				pstmt = null;
				log("PreparedStatement close");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (rs != null) {
			try {
				rs.close();
				rs = null;
				log("ResultSet close");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (dbEngine != null) {
			log(" DBEngine dispose");
			dbEngine.dispose();
		}
	}

	/**
	 * log the information
	 * 
	 * @author tan
	 * @param msg
	 * 
	 *            2010/02/06 10:58:14
	 */
	private void logInfo(String msg) {
		logger.log(Level.INFO, msg);
	}

	private void echo(String msg) {
		System.out.println(msg);
	}

	/**
	 * 
	 * @author tan
	 * @param table
	 *            2010/02/26 9:40:06
	 */
	private void createInsertTigger(TableDescriptor table) {
		StringBuffer symbol = new StringBuffer();
		sql.append("INSERT INTO ").append(table.name).append(" (");
		for (int i = 0; i < table.columns.length; i++) {
			if (i == table.columns.length - 1) {
				sql.append(table.columns[i].name + ')');
				symbol.append('?');
			} else {
				sql.append(table.columns[i].name).append(',');
				symbol.append("?,");
			}
		}
		sql.append(" VALUES ( ").append(symbol).append(" )");
	}

	/**
	 * <pre>
	 * 	prepare the statement , 
	 * 	set the value for the statement
	 * </pre>
	 * 
	 * @author tan
	 * @param table
	 *            the dto's table's descriptor
	 * @param dto
	 *            dto
	 * @throws IllegalAccessException
	 * @throws SQLException
	 *             2010/02/26 15:24:03
	 */
	private void prepareStatement(TableDescriptor table, Object dto) throws IllegalAccessException, SQLException {
		Class<?> clazz = dto.getClass();
		Field[] fs = clazz.getDeclaredFields();
		if (entity != clazz) {
			// logger.log(Level.WARNING,
			// "The object's data can not match the table!");
			logWarning(" 插入的数据不匹配");
			throw new SQLException("The object's data can not match the table!");
		}
		if (fs.length != table.columns.length) {
			logWarning(" 插入的数据项与表中的项不匹配");
			throw new SQLException("The object's fields can not match the table's columns!");
		}
		for (int i = 0; i < fs.length; i++) {
			// System.out.println(fs[i].getName() + "--> " +
			// fs[i].get(o) + " --> " + fs[i].getType());
			Object value = fs[i].get(dto);

			if (value == null &&
					table.columns[i].nullable) {
				throw new SQLException("");
			} else if (value == null &&
					!table.columns[i].nullable) {
				pstmt.setNull(i + 1, table.columns[i].type.value());
			}
			setValue(pstmt, i + 1, fs[i], value, table);
		}
	}

	private void setValue(PreparedStatement pstmt,
			int index, Field field, Object value, TableDescriptor table) throws SQLException {
		// set the value for PrepareStatement
		Class<?> type = field.getType();
		if (type == String.class) {
			pstmt.setString(index, (String) value);
		} else if (type == int.class
				|| type == Integer.class) {
			// TODO check the nullable
			if (table.columns[index - 1].nullable && value == null) {
				pstmt.setInt(index, table.columns[index - 1].decimal);
			} else
				pstmt.setInt(index, (Integer) value);
		} else if (type == float.class
				|| type == Float.class) {
			pstmt.setFloat(index, (Float) value);
		} else if (type == long.class
				|| type == Long.class) {
			pstmt.setLong(index, (Long) value);
		} else if (type == byte.class
				|| type == Byte.class) {
			pstmt.setByte(index, (Byte) value);
		} else if (type == BigDecimal.class) {
			pstmt.setBigDecimal(index, (BigDecimal) value);
		} else if (type == boolean.class
				|| type == Boolean.class) {
			pstmt.setBoolean(index, (Boolean) value);
		} else if (type == java.sql.Date.class) {
			pstmt.setDate(index, (java.sql.Date) value);
		} else if (type == Time.class) {
			pstmt.setTime(index, (Time) value);
		} else if (type == Timestamp.class) {
			pstmt.setTimestamp(index, (Timestamp) value);
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		} else if (InputStream.class.isAssignableFrom(type)) {
			pstmt.setBinaryStream(index, (InputStream) value);
		} else if (Reader.class.isAssignableFrom(type)) {
			pstmt.setCharacterStream(index, (Reader) value);
		} else if (Blob.class.isAssignableFrom(type)) {
			pstmt.setBlob(index, (Blob) value);
		} else if (Clob.class.isAssignableFrom(type)) {
			pstmt.setClob(index, (Clob) value);
		} else if (type == URL.class) {
			pstmt.setURL(index, (URL) value);
		} else if (type == URI.class || URI.class.isAssignableFrom(type)) {
			try {
				URL url = ((URI) value).toURL();
				pstmt.setURL(index, url);
			} catch (MalformedURLException e) {
				e.printStackTrace();
			}
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		}

		String buf = sqlInfo.toString().replaceFirst("\\?", value.toString());
		sqlInfo = new StringBuilder(buf);
	}

	private StringBuilder sqlInfo = new StringBuilder();

	private void setValueBackup(PreparedStatement pstmt,
			int index, Field field, Object value, TableDescriptor table) throws SQLException {
		// set the value for PrepareStatement
		Class<?> type = field.getType();
		if (type == String.class) {
			pstmt.setString(index, (String) value);
		} else if (type == int.class
				|| type == Integer.class) {
			// TODO check the nullable
			if (table.columns[index - 1].nullable && value == null) {
				pstmt.setInt(index, table.columns[index - 1].decimal);
			} else
				pstmt.setInt(index, (Integer) value);
		} else if (type == float.class
				|| type == Float.class) {
			pstmt.setFloat(index, (Float) value);
		} else if (type == long.class
				|| type == Long.class) {
			pstmt.setLong(index, (Long) value);
		} else if (type == byte.class
				|| type == Byte.class) {
			pstmt.setByte(index, (Byte) value);
		} else if (type == BigDecimal.class) {
			pstmt.setBigDecimal(index, (BigDecimal) value);
		} else if (type == boolean.class
				|| type == Boolean.class) {
			pstmt.setBoolean(index, (Boolean) value);
		} else if (type == java.sql.Date.class) {
			pstmt.setDate(index, (java.sql.Date) value);
		} else if (type == Time.class) {
			pstmt.setTime(index, (Time) value);
		} else if (type == Timestamp.class) {
			pstmt.setTimestamp(index, (Timestamp) value);
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		} else if (InputStream.class.isAssignableFrom(type)) {
			pstmt.setBinaryStream(index, (InputStream) value);
		} else if (Reader.class.isAssignableFrom(type)) {
			pstmt.setCharacterStream(index, (Reader) value);
		} else if (Blob.class.isAssignableFrom(type)) {
			pstmt.setBlob(index, (Blob) value);
		} else if (Clob.class.isAssignableFrom(type)) {
			pstmt.setClob(index, (Clob) value);
		} else if (type == URL.class) {
			pstmt.setURL(index, (URL) value);
		} else if (type == URI.class || URI.class.isAssignableFrom(type)) {
			try {
				URL url = ((URI) value).toURL();
				pstmt.setURL(index, url);
			} catch (MalformedURLException e) {
				e.printStackTrace();
			}
		} else if (type == byte[].class ||
				type == Byte[].class) {
			pstmt.setBytes(index, (byte[]) value);
		}
	}

	/**
	 * prepare the statement
	 * 
	 * @author tan 2010/03/01 10:48:05
	 * @throws ClassNotFoundException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 * @throws SQLException
	 */
	private void prepare() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		pstmt = getDBEngine().getConnection().prepareStatement(sql.toString());
	}
	
	public List<T> selectNative(String nativeSql) {
		List<T> list = new ArrayList<T>();
		sql.append(nativeSql);
		// prepare the statement
		try {
			prepare();
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}

		try {
			rs = pstmt.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				Object result = null;
				result = entity.newInstance();
				Field[] fields = result.getClass().getFields();
				for (int i = 0; i < fields.length; i++) {
					fields[i].set(result, rs.getObject(i + 1));
				}
				list.add((T) result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public boolean connect() {
		// TODO oracle 判断是否可以连接上.
		return false;
	}

	public List selectNative(String sql, boolean isReturnList) {
		// TODO Auto-generated method stub
		return null;
	}

	public T selectByPrimaryKey(Object... objects) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<?> script(String script) {
		// TODO Auto-generated method stub
		return null;
	}
}
